package com.situ.report.dao;

import com.situ.report.entity.gx;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface Gxdao {
    @Select("<script>" +
            "select distinct cst_customer.cust_no,cst_customer.cust_name label,SUM(orders_line.odd_price) value,orders.odr_date  from cst_customer\n" +
            "inner join orders on cst_customer.cust_no=orders.odr_customer_id\n" +
            "inner join orders_line on orders_line.odd_order_id=orders.odr_id\n" +
            "<where> " +
            "<if test=\"year!=null and year!=''\">\n" +
            " and year(orders.odr_date)=#{year}" +
            "  </if>\n" +
            "<if test=\"label!=null and label!=''\">\n" +
            " and cst_customer.cust_name like '%${label}%'" +
            "  </if>" +
            "</where> " +
            "group by cst_customer.cust_no,cst_customer.cust_name,orders.odr_date"+
            "</script>")
    public List<gx> queryall(gx g);

    @Select("\t\t\tselect distinct DATENAME(YEAR, orders.odr_date) year  from cst_customer \n" +
            "\tinner join orders on cst_customer.cust_no=orders.odr_customer_id\n" +
            "\t\tinner join orders_line on orders_line.odd_order_id=orders.odr_id\n" +
            "\t\t\n" +
            "\t\t\tgroup by cst_customer.cust_no,cst_customer.cust_name,orders.odr_date\n"
    +"order by DATENAME(YEAR,orders.odr_date) desc")
    public List<gx> queryyear();
}
